﻿using CarCommentCrawling.Tool;
using CategoryClassify.Model;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CategoryClassify
{
    class Program
    {
        private static string _currentPath;
        static void Main(string[] args)
        {
            #region 导入期刊
             _currentPath = System.Environment.CurrentDirectory;
            string filePath = $"{_currentPath}\\file\\期刊目录修改.xlsx";
            var excel = new NpoiExcelHelp(filePath);
            var dataTable = excel.ExcelToDataTable("期刊总库20211215", true);
            List<PeriodicalEntity> insertPeriodicalList = new List<PeriodicalEntity>();
            foreach (DataRow row in dataTable.Rows)
            {
               
                PeriodicalEntity periodicalEntity = new PeriodicalEntity
                {
                    PeriodicalName = row["中文刊名"].ToString(),
                    MainUnity = row["主办单位"].ToString(),
                    ClassifyIds = row["分类号"].ToString().Split(';', ',').ToList(),
                    ClassifyId = row["分类号"].ToString(),
                };
                insertPeriodicalList.Add(periodicalEntity);
            }
            #endregion

            #region 导入图书分类
            string bookClassifyStatisticFilePath = $"{_currentPath}\\file\\图书分类号统计表（参照）.xlsx";
            var exce2 = new NpoiExcelHelp(bookClassifyStatisticFilePath);
            var dataTable2 = exce2.ExcelToDataTable("Sheet1", true);
            List<BooKClassifyStatistic> bookClassifyStatistics = new List<BooKClassifyStatistic>();
            foreach (DataRow dataRow in dataTable2.Rows)
            {
                BooKClassifyStatistic booKClassifyStatistic = new BooKClassifyStatistic
                {
                    SystemDepartment = dataRow[0].ToString(),
                    MajorName = dataRow[1].ToString(),
                    ClassifyIds = dataRow[2].ToString().Split('、').ToList(),
                    FallbackClassifyIds = dataRow[3].ToString(),
                    periodicalEntities = new List<PeriodicalEntity>()
                };
                if (booKClassifyStatistic.SystemDepartment.Equals("备注：Z类图书按照实际专业挑选。"))
                    continue;
                bookClassifyStatistics.Add(booKClassifyStatistic);
            }
            #endregion

            bookClassifyStatistics.ForEach(f => {
                f.ClassifyIds.ForEach(classifyid =>
                {
                    foreach (var periodicalItem in insertPeriodicalList)
                    {
                        if (periodicalItem.ClassifyIds.Contains(classifyid))
                        {
                            if (!f.periodicalEntities.Contains(periodicalItem))
                            {
                                f.periodicalEntities.Add(periodicalItem);
                            }
                        }
                    }
                });
            });
            //数据导出
            ExcelExport(bookClassifyStatistics);
        }
        /// <summary>
        /// 汇总导出
        /// </summary>
        /// <param name="bookClassifyStatistics"></param>
        private static void ExcelExport(List<BooKClassifyStatistic> bookClassifyStatistics)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");

            int cellIndex = 0;
            IRow headerRow = sheet.CreateRow(0);
            headerRow.HeightInPoints = 35;
            List<string> headList = new List<string>() { "系部", "专业名称", "图书分类号", "备选分类号", "分类号", "中文刊名", "主办单位" };

            #region 字体
            IFont font = workbook.CreateFont();
            //字体大小
            font.FontHeightInPoints = 12;
            font.Boldweight = 360;

            #endregion
            for (int i = 0; i < headList.Count; i++)
            {
                string ColumnsName = headList[i];
                sheet.SetColumnWidth(cellIndex, 5000);
                headerRow.CreateCell(cellIndex).SetCellValue(ColumnsName);

                ICellStyle headStyle = workbook.CreateCellStyle();
                headStyle.WrapText = true;
                headStyle.SetFont(font);
                headerRow.GetCell(cellIndex).CellStyle = headStyle;
                cellIndex++;
            }

            int rowIndex = 1;//行数一定要从1行开始
            int count = 1;
            int startRow = 1;
            bookClassifyStatistics.ForEach(item =>
            {
                int rowcout = 0;
                for (int i = 0; i < item.periodicalEntities.Count; i++)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);

                    ICell newCe = dataRow.CreateCell(0);
                    newCe.SetCellValue(item.SystemDepartment);
                    ICell newCel = dataRow.CreateCell(1);
                    newCel.SetCellValue(item.MajorName);
                    ICell newCe2 = dataRow.CreateCell(2);
                    newCe2.SetCellValue(string.Join("、", item.ClassifyIds));
                    ICell newCe3 = dataRow.CreateCell(3);
                    newCe3.SetCellValue(string.Join("、", item.ClassifyIds));
                    ICell newCe4 = dataRow.CreateCell(4);
                    newCe4.SetCellValue($"{item.periodicalEntities[i].ClassifyId}");
                    ICell newCe5 = dataRow.CreateCell(5);
                    newCe5.SetCellValue($"{item.periodicalEntities[i].PeriodicalName}");
                    ICell newCel6 = dataRow.CreateCell(6);
                    newCel6.SetCellValue($"{item.periodicalEntities[i].MainUnity}");
                    rowIndex++;
                    rowcout++;
                }
                if (rowcout != 0)
                {
                    if (count == 1)
                    {
                        //合并行数

                        sheet.AddMergedRegion(new CellRangeAddress(startRow, rowcout, 0, 0));
                        sheet.AddMergedRegion(new CellRangeAddress(startRow, rowcout, 1, 1));
                        sheet.AddMergedRegion(new CellRangeAddress(startRow, rowcout, 2, 2));
                        sheet.AddMergedRegion(new CellRangeAddress(startRow, rowcout, 3, 3));
                        startRow = startRow + rowcout;
                    }
                    else
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(startRow, startRow + rowcout - 1, 0, 0));
                        sheet.AddMergedRegion(new CellRangeAddress(startRow, startRow + rowcout - 1, 1, 1));
                        sheet.AddMergedRegion(new CellRangeAddress(startRow, startRow + rowcout - 1, 2, 2));
                        sheet.AddMergedRegion(new CellRangeAddress(startRow, startRow + rowcout - 1, 3, 3));
                        startRow = startRow + rowcout;
                    }
                }
                count++;
            });


            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            var buf = stream.ToArray();
            var exportPath = $"{_currentPath}\\file\\汇总数据.xlsx";
            if (File.Exists(exportPath))

                File.Delete(exportPath);
            using (FileStream fs = new FileStream(exportPath, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }
            Console.WriteLine($"导出路径：{exportPath}");
            Console.ReadLine();
        }
    }
}
